package be.webchess.www.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import be.webchess.www.model.ChessBoard;
import be.webchess.www.model.ChessPiece;
import be.webchess.www.model.User;

public class ChessBoardDAO extends GenericDAO<ChessBoard> {

	UserDAO			userDAO;
	ChessPieceDAO	pieceDAO;
	
	public ChessBoardDAO(Connection c) {
		super(c);

		userDAO = new UserDAO(c);
		pieceDAO = new ChessPieceDAO(c);
	}

	@Override
	public ChessBoard findByID(Long id) throws SQLException {

		ChessBoard board = null;
		
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("b.id, ");
		query.append("b.flags, ");
		query.append("b.white_player, ");
		query.append("b.black_player, ");
		query.append("b.next_player_color ");
		query.append("FROM chessboard b ");
//		query.append("INNER JOIN piece p on p.id=b.idUser ");
		query.append("WHERE b.id=? ");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setLong(1, id);

		ResultSet result = stmt.executeQuery();
		if(result.next()) {
			board = new ChessBoard();
			buildObject(result, board);
		}
				
		return board;
	}

	@Override
	public ChessBoard insert(ChessBoard o) throws SQLException {
		
		StringBuffer query = new StringBuffer();
		query.append("INSERT INTO chessboard(");
		query.append("flags, ");
		query.append("next_player_color, ");
		query.append("white_player, ");
		query.append("black_player ");
		query.append(") VALUES (?,?)");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
//		query.append("WHERE u.id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString(),Statement.RETURN_GENERATED_KEYS);
		stmt.setLong(1, o.getFlags());
		stmt.setString(2, o.getNextPlayerColor());
		stmt.setLong(3, o.getWhitePlayer().getId());
		stmt.setLong(4, o.getBlackPlayer().getId());
		
		stmt.executeUpdate();
		
		ResultSet result = stmt.getGeneratedKeys();
		
		if(result.next()) {
			o.setId(result.getLong(1));
		}
		
		return o;
	}

	@Override
	public ChessBoard update(ChessBoard o) throws SQLException {

		StringBuffer query = new StringBuffer();
		query.append("UPDATE chessboard SET ");
		query.append("flags=?, ");
		query.append("next_player_color=?, ");
		query.append("white_player=?, ");
		query.append("black_player=? ");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
		query.append("WHERE id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setLong(1, o.getFlags());
		stmt.setString(2, o.getNextPlayerColor());
		stmt.setLong(3, o.getWhitePlayer().getId());
		stmt.setLong(4, o.getBlackPlayer().getId());		
		stmt.setLong(5, o.getId());
		
		stmt.executeUpdate();
		
		return o;
	}

	@Override
	public void delete(ChessBoard o) throws SQLException {

		StringBuffer query = new StringBuffer();
		query.append("DELETE FROM chessboard ");
		query.append("WHERE id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setLong(1, o.getId());
		stmt.executeUpdate();

	}

	@Override
	public List<ChessBoard> findAll() throws SQLException {

		LinkedList<ChessBoard> results = new LinkedList<ChessBoard>();
		
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("b.id, ");
		query.append("b.flags, ");
		query.append("b.white_player, ");
		query.append("b.black_player, ");
		query.append("b.next_player_color ");
		query.append("FROM chessboard b ");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
//		query.append("WHERE u.id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		
		ResultSet result = stmt.executeQuery();
		while(result.next()) {
			ChessBoard piece = new ChessBoard();
			buildObject(result, piece);
			results.add(piece);
		}
		
		return results;
	}

	private void buildObject(ResultSet result, ChessBoard board) throws SQLException{
		
		int		col = 1;
		Map<String, ChessPiece> matrix = new TreeMap<String, ChessPiece>();

		board.setId(result.getLong(col++));
		board.setFlags(result.getInt(col++));
		board.setWhitePlayer(userDAO.findByID(result.getLong(col++)));
		board.setBlackPlayer(userDAO.findByID(result.getLong(col++)));
		board.setNextPlayerColor(result.getString(col++));
		
		List<ChessPiece> pieces = pieceDAO.findByBoardID(board.getId());
		
		for ( ChessPiece p : pieces )
		{
			p.setBoard(board);
			matrix.put(p.getLocation(), p);
		}
		
		board.setMatrix(matrix);
		board.setPieces(pieces);
	}

}
